USE language_classes;

CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(45) NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `middle_name` varchar(45),
  `birthday` date NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `phone_number` varchar(20) DEFAULT '',
  `email` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `language` (
  `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `level` (
  `id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  `description` VARCHAR(200) DEFAULT '',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `teacher` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `last_name` varchar(45) NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `middle_name` varchar(45) DEFAULT '',
  `birthday` date NOT NULL,
  `sex` tinyint(1) NOT NULL,
  `phone_number` varchar(20) DEFAULT '',
  `email` varchar(30) DEFAULT '',
  `language_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_teacher_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`id`) ON UPDATE CASCADE
) DEFAULT CHARSET=utf8;

CREATE TABLE `teacher_level` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `teacher_id` int(10) unsigned NOT NULL,
  `level_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_teacher_level_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_teacher_level_level` FOREIGN KEY (`level_id`) REFERENCES `level` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARSET=utf8;

CREATE TABLE `class` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `level_id` int(10) unsigned NOT NULL,
  `teacher_id` int(10) unsigned DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `description` varchar(200) DEFAULT '',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_class_level` FOREIGN KEY (`level_id`) REFERENCES `level` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_class_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) DEFAULT CHARSET=utf8;

CREATE TABLE `class_student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `class_id` int(10) unsigned NOT NULL,
  `student_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_class_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_class_student_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARSET=utf8;

DELIMITER //
CREATE TRIGGER update_class_check BEFORE UPDATE ON class
FOR EACH ROW
BEGIN
	IF NEW.start_date >= NEW.end_date THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Wrong dates.';
	END IF; 
    
    IF NEW.level_id NOT IN (SELECT level_id FROM teacher_level WHERE teacher_id = NEW.teacher_id) THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'The teacher cant teach this language.';
	END IF;
END//

CREATE TRIGGER insert_class_check BEFORE INSERT ON class
FOR EACH ROW
BEGIN
	IF NEW.start_date >= NEW.end_date THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Wrong dates.';
	END IF;
    
	IF NEW.level_id NOT IN (SELECT level_id FROM teacher_level WHERE teacher_id = NEW.teacher_id) THEN
		SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'The teacher cant teach this language.';
	END IF;
END//
DELIMITER ;


